package rpgsdb.db;

import java.io.File;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;
import javax.swing.JOptionPane;

/*Author: John Archer, Contact: john.archer.jpa@gmail.com*/
public class RPGSDB {
    public RPGSDB(boolean newdb) {this("db", newdb);}
    
    public RPGSDB(String addressBookName, boolean newdb) {
        this.dbName = addressBookName;
        setDBSystemDir();
        dbProps = loadDBProperties();
        String driverName = dbProps.getProperty("derby.driver");
        loadDatabaseDriver(driverName);
        if(!dbExists() || newdb){createNew();}
        else{
            try{loadDatabase();}
            catch(Exception e){
                JOptionPane.showMessageDialog(null, "The program is already running!", "alert", JOptionPane.ERROR_MESSAGE);
                System.exit(0);
            }
        }
    }
    
    public void clearTables(){
        String dbLocation = getDatabaseLocation();
        File dbFileDir = new File(dbLocation);
        if (dbFileDir.exists()) {
            String dbUrl = getDatabaseUrl();
            dbProps.put("create", "true");
            try {
                con = DriverManager.getConnection(dbUrl, dbProps);
                Statement statement = con.createStatement();
                try{statement.execute("DROP TABLE APP.DATASET");}
                catch(Exception e){}
                try{statement.execute("DROP TABLE APP.PROJECT");}
                catch(Exception e){}
                statement.execute(createDataset);
                statement.execute(createProject);
            }catch(Exception e){}
        }
    }

    public void createNew(){
        if(!dbExists()){
            String dbLocation = getDatabaseLocation();
            File dbFileDir = new File(dbLocation);
            if (dbFileDir.exists()){dbFileDir.delete();}
        }
        create();
    }
    
    private boolean dbExists() {
        boolean bExists = false;
        String dbLocation = getDatabaseLocation();
        File dbFileDir = new File(dbLocation);
        if (dbFileDir.exists()){bExists = true;}
        return bExists;
    }

    private void setDBSystemDir() {
        String systemDir = System.getProperty(".", ".") + "/db";
        System.setProperty("derby.system.home", System.getProperty(".", ".") + "/db");
        new File(systemDir).mkdir();
    }

    private void loadDatabaseDriver(String driverName) {
        try{Class.forName(driverName);}
        catch(ClassNotFoundException ex){ex.printStackTrace();}
    }

    private Properties loadDBProperties() {
        dbProps = new Properties();
        dbProps.setProperty("user","addressuser");
        dbProps.setProperty("password","addressuser");
        dbProps.setProperty("derby.driver","org.apache.derby.jdbc.EmbeddedDriver");
        dbProps.setProperty("derby.url","jdbc:derby:");
        dbProps.setProperty("db.schema","APP");
        return dbProps;
    }

    private boolean loadDatabase() throws Exception{
        boolean bCreated = false;
        con = null;
        String dbUrl = getDatabaseUrl();
        dbProps.put("create", "true");
        con = DriverManager.getConnection(dbUrl, dbProps);
        dbProps.remove("create");
        return bCreated;
    }
    
    private boolean create() {
        boolean create = false;
        con = null;
        String dbUrl = getDatabaseUrl();
        dbProps.put("create", "true");
        try {
            con = DriverManager.getConnection(dbUrl, dbProps);
            Statement statement = con.createStatement();
            try{statement.execute("DROP TABLE APP.DATASET");}
            catch(Exception e){}
            try{statement.execute("DROP TABLE APP.PROJECT");}
            catch(Exception e){}
            statement.execute(createDataset);
            statement.execute(createProject);
            create = true;
        } 
        catch (SQLException ex) {}
        dbProps.remove("create");
        return create;
    }

    public String getDatabaseLocation() {return System.getProperty("derby.system.home") + "/" + dbName;}

    public String getDatabaseUrl() {return  dbProps.getProperty("derby.url") + dbName;}

    protected Connection con;
    protected Properties dbProps;
    private String dbName;

    private static final String createDataset =
            "create table APP.DATASET (" +
            "ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
            "TITLE VARCHAR(100) NOT NULL, " +
            "TITLE_PATH VARCHAR(400) NOT NULL, " +
            "PROJECT_ID INT NOT NULL " +
            ")";

    private static final String createProject =
            "create table APP.PROJECT (" +
            "ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
            "TITLE VARCHAR(100) NOT NULL, " +
            "WORD_SIZE INT NOT NULL, " +
            "READ_KMER_QUALITY INT NOT NULL, " +
            "WORD_SKIP INT NOT NULL, " +
            "MATCH_SCORE DOUBLE NOT NULL, " +
            "GAP_OPEN_SCORE DOUBLE NOT NULL, " +
            "GAP_EXTENTION DOUBLE NOT NULL, " +
            "TRANSVERSION_SCORE DOUBLE NOT NULL, " +
            "TRANSITION_SCORE DOUBLE NOT NULL, " +
            "MINIMUM_READ_LENGTH_TO_KEEP INT NOT NULL, " +
            "MAXIMUM_READ_LENGTH_TO_KEEP INT NOT NULL, " +
            "MAX_INDEL_SIZE_TO_COUNT INT NOT NULL, " +
            "TEMPLATE_TITLE VARCHAR(200) NOT NULL, " +
            "TEMPLATE VARCHAR(20000) NOT NULL " +
            ")";

}